import pymysql as sql
from flask import jsonify

connect = sql.Connect(
    host='rm-bp131vjnd9b99vmuq2o.mysql.rds.aliyuncs.com',
    port=3306,
    user='lin_432718',
    passwd='Lin817818',
    db='rail_traffic',
    charset='utf8'
)
cursor = connect.cursor()


def getAllStaff():
    print('return all staff')
    sql = "SELECT * FROM staff"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有职员")
        return 0
    else:
        print("查询成功")
        return temp


def newstaff(id, name, phone):
    print('insert a new staff')

    sql = "insert into staff values( '" + id + "', '" + name + "','" + phone + "')"
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def editstaff(id, name, phone):
    print('edit staff')
    if phone:
        sql = "update staff set staff_name='" + name + "',staff_phone='" + phone + "' where staff_id='" + id + "'"
    else:
        sql = "update staff set staff_name='" + name + "' where staff_id='" + id + "'"
    print(sql)

    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def deleteStaff(word):
    sql = "Delete FROM staff WHERE staff_id =" + str(word)
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def searchStaff(word):
    sql = "SELECT * FROM staff WHERE staff_name LIKE '%" + word + "%' or staff_id LIKE '%" + word + "%'"
    print(sql)
    cursor.execute(sql)

    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有符合条件的职员")
        return 0
    else:
        print("搜索成功")
        return temp


def getName(admin):
    sql = "select staff_id, staff_name from staff WHERE staff_id = '" + admin + "'"
    # print(sql)
    cursor.execute(sql)
    attribute = ['userName', 'staffName']

    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)
    # print(l)
    return jsonify(l)

